<?php

/**
 * This file is part of the Codizer package.
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 *
 * @copyright   Copyright (c) 2010 Advisa (http://www.advisa.fr)
 * @author      Codizer Core Team <team@codizer.org>
 * @package     Codizer
 */

abstract class f_database_AbstractQueryBuilder
{
	protected $query;
	protected $conditions;
	protected $orderConditions;
	protected $publicationDatesCondition;
	protected $values;
	protected $fieldsToGet;
	protected $columnsToGet;

	protected $fieldsToGetString;
	protected $columnsToGetString;
	protected $joinTableFieldsToGetString;

	protected $tableName;

	protected $groupFields;
	protected $limitNbResult;
	protected $limitOffset;

    protected $distinct = false;
    protected $relationQuery = array();

// ================================= Conditions methods

	/**
	 * Add a new = condition in SELECT query
	 * To add an OR condition just pass an array to $value
	 *
	 * @param string $fieldName
	 * @param mixed $value
	 */
	public function addEqualCondition($fieldName, $value, $andOr = 'AND')
	{
		if(!is_array($value))
		{
            $varName = isset($this->conditions[$andOr]) ? $fieldName.count($this->conditions[$andOr]) : $fieldName.'0';
			$this->conditions[$andOr][] = "`$fieldName` = :eq".$varName;
			$this->values['eq'.$varName] = $value;
			$this->fieldsToGet[] = $fieldName;
		}
		else
		{
            $varName = $fieldName.count($this->conditions);
			$conditions = array();
			foreach($value as $key => $val)
			{
				$conditions[] = ':eq'.$varName.$key;
				$this->values['eq'.$varName.$key] = $val;
			}

            $this->conditions[$andOr][] = "`$fieldName` IN(".join(',',$conditions).")";
            $this->fieldsToGet[] = $fieldName;
		}
	}

	/**
	 * Add a new != condition in SELECT query
	 * To add an OR condition just pass an array to $value
	 *
	 * @param string $fieldName
	 * @param mixed $value
	 */
	public function addNotEqualCondition($fieldName, $value, $andOr = 'AND')
	{
		if(!is_array($value))
		{
            $varName = $fieldName.count($this->conditions);
			$this->conditions[$andOr][] = "`$fieldName` != :ne".$varName;
			$this->values['ne'.$varName] = $value;
			$this->fieldsToGet[] = $fieldName;
		}
		else
		{
            $varName = $fieldName.count($this->conditions);
			$conditions = array();
			foreach($value as $key => $val)
			{
				$conditions[] = ':ne'.$varName.$key;
				$this->values['ne'.$varName.$key] = $val;
			}

            $this->conditions[$andOr][] = "`$fieldName` NOT IN(".join(',',$conditions).")";
            $this->fieldsToGet[] = $fieldName;
		}
	}

	/**
	 * Add a new < condition in SELECT query
	 *
	 * @param string $fieldName
	 * @param mixed $value
	 */
	public function addLessThanCondition($fieldName, $value, $andOr = 'AND')
	{
		$this->conditions[$andOr][] = "`$fieldName` < :lt".$fieldName;
		$this->values['lt'.$fieldName] = $value;
		$this->fieldsToGet[] = $fieldName;
	}

	/**
	 * Add a new > condition in SELECT query
	 *
	 * @param string $fieldName
	 * @param mixed $value
	 */
	public function addMoreThanCondition($fieldName, $value, $andOr = 'AND')
	{
		$this->conditions[$andOr][] = "`$fieldName` > :mt".$fieldName;
		$this->values['mt'.$fieldName] = $value;
		$this->fieldsToGet[] = $fieldName;
	}

	/**
	 * Add a new <= condition in SELECT query
	 *
	 * @param string $fieldName
	 * @param mixed $value
	 */
	public function addLessThanOrEqualCondition($fieldName, $value, $andOr = 'AND')
	{
		$this->conditions[$andOr][] = "`$fieldName` <= :le".$fieldName;
		$this->values['le'.$fieldName] = $value;
		$this->fieldsToGet[] = $fieldName;
	}

	/**
	 * Add a new >= condition in SELECT query
	 *
	 * @param string $fieldName
	 * @param mixed $value
	 */
	public function addMoreThanOrEqualCondition($fieldName, $value, $andOr = 'AND')
	{
		$this->conditions[$andOr][] = "`$fieldName` >= :me".$fieldName;
		$this->values['me'.$fieldName] = $value;
		$this->fieldsToGet[] = $fieldName;
	}

	/**
	 * Add a new LIKE condition in SELECT query
	 * To add an OR condition just pass an array to $value
	 *
	 * @param string $fieldName
	 * @param mixed $value
	 */
	public function addLikeCondition($fieldName, $value, $andOr = 'AND')
	{
		if(!is_array($value))
		{
			$this->conditions[$andOr][] = "`$fieldName` LIKE :li".$fieldName;
			$this->values['li'.$fieldName] = $value;
			$this->fieldsToGet[] = $fieldName;
		}
		else
		{
			$conditions = array();
			foreach($value as $key => $val)
			{
				$conditions[] =  "`$fieldName` LIKE :li".$fieldName.$key;
				$this->values['li'.$fieldName.$key] = $val;
			}

			$this->conditions[$andOr][] = "(".join(" OR ", $conditions).")";
			$this->fieldsToGet[] = $fieldName;
		}
	}

	/**
	 * Add a new BETWEEN condition in SELECT query
	 *
	 * @param string $fieldName
	 * @param date $dateBegin
	 * @param date $dateEnd
	 */
	public function addDateBetweenCondition($fieldName, $dateBegin, $dateEnd, $andOr = 'AND')
	{
		$counter = count($this->conditions);
		$this->conditions[$andOr][] = "`$fieldName` BETWEEN :dateBegin".$counter." AND :dateEnd".$counter;
		$this->values['dateBegin'.$counter] = $dateBegin;
		$this->values['dateEnd'.$counter] = $dateEnd;
		$this->fieldsToGet[] = $fieldName;
	}

	/**
	 * Add a new > date condition in SELECT query
	 *
	 * @param string $fieldName
	 * @param date $dateBegin
	 */
	public function addDateAfterCondition($fieldName, $dateBegin, $andOr = 'AND')
	{
		$counter = count($this->conditions);
		$this->conditions[$andOr][] = "`$fieldName` > :dateBegin".$counter;
		$this->values['dateBegin'.$counter] = $dateBegin;
		$this->fieldsToGet[] = $fieldName;
	}

	/**
	 * Add a new < date condition in SELECT query
	 *
	 * @param string $fieldName
	 * @param date $dateBegin
	 */
	public function addDateBeforeCondition($fieldName, $dateEnd, $andOr = 'AND')
	{
		$counter = count($this->conditions);
		$this->conditions[$andOr][] = "`$fieldName` < :dateEnd".$counter;
		$this->values['dateEnd'.$counter] = $dateEnd;
		$this->fieldsToGet[] = $fieldName;
	}

	/**
	 * Add a GROUP BY to the query
	 *
	 * @param array $fieldNames
	 */
	public function addGroupBy($fieldNames)
	{
		$this->groupFields = $fieldNames;
	}

	/**
	 * Add a DISTINCT to the query
	 *
	 * @param array $fieldNames
	 */
    public function addDistinct()
    {
        $this->distinct = true;
    }

	/**
	 * Add a LIMIT to the query
	 *
	 * @param $lineNumber
	 * @param $firstLine
	 * @return unknown_type
	 */
	public function addLimit($nbResult, $offset = 0)
	{
		$this->limitNbResult = $nbResult;
		$this->limitOffset = $offset;
	}

// ============================== Order

	/**
	 * Add an ORDER ASC in SELECT query
	 *
	 * @param string $fieldName
	 */
	public function addAscOrder($fieldName)
	{
		$this->orderConditions[] = "`$fieldName` ASC";
		$this->fieldsToGet[] = $fieldName;
	}

	/**
	 * Add an ORDER DESC in SELECT query
	 *
	 * @param string $fieldName
	 */
	public function addDescOrder($fieldName)
	{
		$this->orderConditions[] = "`$fieldName` DESC";
		$this->fieldsToGet[] = $fieldName;
	}

// ============================== Getting methods

	/**
	 * Add a column to get in SELECT query
	 *
	 * @param string $column
	 */
	public function addColumnToGet($column)
	{
		$this->columnsToGet[] = $column;

		if(!preg_match("/COUNT(.?)/", $column))
		{
			$this->fieldsToGet[] = $column;
		}
        else
        {
            $this->fieldsToGet[] = 'document_id';
        }
        return $this;
	}

	/**
	 * Retrieve the SELECT query
	 *
	 * @return string SQL query
	 */
	public function getQuery()
	{
        $this->buildSelectQuery();
        $this->buildWhereConditions();
        $this->buildGroupBy();
        $this->buildOrderConditions();
        $this->buildLimit();

        return $this->query;
	}

	/**
	 * Return all values to get with the SELECT query
	 * format : array[PDOsqlmask] = valueToGet
	 *
	 * @return array
	 */
	public function getValues()
	{
		return $this->values;
	}

// ============================== Building methods

	/**
	 * Build the SELECT query beginning
	 * returns : SELECT fields
	 */
	protected function buildSelectQuery()
	{
		$this->query .= "SELECT ";

		$this->columnsToGetString = join(", ", array_unique($this->columnsToGet));
		$this->fieldsToGetString = join(", ", array_unique($this->fieldsToGet));
        if($this->distinct == true)
        {
            $this->columnsToGetString = "DISTINCT " . $this->columnsToGetString;
        }

		$this->query .= $this->columnsToGetString;

		$this->buildFrom();
	}

	/**
	 * Add GROUP BY to query
	 */
	protected function buildGroupBy()
	{
		if(count($this->groupFields)>0)
		{
			$sql = " GROUP BY ".join(", ",$this->groupFields);
			$this->query .= $sql;
		}
	}

	/**
	 * Add LIMIT to query
	 */
	protected function buildLimit()
	{
		if(isset($this->limitNbResult))
		{
			if(isset($this->limitOffset) && $this->limitOffset != 0)
			{
				$this->query .= " LIMIT ".$this->limitOffset.",".$this->limitNbResult;
			}
			else
			{
				$this->query .= " LIMIT ".$this->limitNbResult;
			}
		}
	}

	/**
	 * Build the select FROM
	 * returns : FROM tableName or FROM joinedTables
	 */
	protected function buildFrom()
	{
		$this->query .= " FROM ";
		$this->query .= $this->tableName;
	}

	/**
	 * Build all joined tables columns
	 * Make the difference in fields to get by table (i18n or not)
	 */
	protected function buildJoinTableColumnsToGet()
	{
		$this->joinTableFieldsToGetString = join(", ", $this->fieldsToGet);
	}

	/**
	 * Build the WHERE conditions
	 * Make the difference in fields to get by table (i18n or not)
	 */
	protected function buildWhereConditions()
	{
        $this->query .= " WHERE ";

		if(count($this->conditions) >= 1)
		{
            if(isset($this->conditions['AND']) && count($this->conditions['AND']) >= 1)
            {
                $this->query .=  join(" AND ", $this->conditions['AND']);

                if(isset($this->publicationDatesCondition) && $this->publicationDatesCondition != "")
                {
                    $this->query .= " AND ".$this->publicationDatesCondition;
                }
            }
            
            if(isset($this->conditions['OR']) && count($this->conditions['OR']) >= 1)
            {
                $this->query .= ' AND (';
                $this->query .=  join(" OR ", $this->conditions['OR']);
                $this->query .= ')';
            }
		}
		else
		{
			if(isset($this->publicationDatesCondition) && $this->publicationDatesCondition != "")
			{
				$this->query .= $this->publicationDatesCondition;
			}
		}
	}

	/**
	 * Build the ORDER BY conditions
	 * Make the difference in fields to get by table (i18n or not)
	 */
	protected function buildOrderConditions()
	{
		if(count($this->orderConditions) >= 1)
		{
			$this->query .= " ORDER BY ";            
            $this->query .=  join(", ", $this->orderConditions);
		}
	}
}